﻿
Imports System.Data.SqlClient
Imports System.Data

Imports System.Data.OleDb

Public Class clsCommand

    Shared conn As String = System.Configuration.ConfigurationManager.AppSettings("dbConnection")

    Public Shared Function SQL_exec(ByVal sqlstr As String) As Data.DataTable

        Dim objConn As New OleDb.OleDbConnection
        Dim Command As OleDb.OleDbDataAdapter
        Dim MyTable As New DataTable


        objConn = New OleDb.OleDbConnection(conn)
        objConn.Open()

        Command = New OleDb.OleDbDataAdapter(sqlstr, objConn)
        Command.Fill(MyTable)

        Command = Nothing
        objConn.Close()
        objConn = Nothing

        Return MyTable
    End Function

    Public Shared Function SQL_cmd(ByVal sqlstr As String) As String ' insert update delete

        Dim Command As New OleDb.OleDbCommand
        Dim objConn As New OleDb.OleDbConnection
        Dim Trans As OleDbTransaction
        Dim err As String = ""

        objConn = New OleDbConnection(conn)
        objConn.Open()

        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted) ' ** start transaction

        Command.Connection = objConn
        Command.Transaction = Trans ' assign transaction to command

        Try
            Command.CommandText = sqlstr
            '  Command.CommandType = CommandType.Text
            Command.ExecuteNonQuery()
            Trans.Commit()
        Catch ex As Exception
            err = ex.Message.ToString
            Trans.Rollback()
            Throw
        Finally
            objConn.Close()
        End Try

        Return err
    End Function

  
End Class
